#!/usr/bin/env python
#-*- encoding:UTF-8 -*-

# $Id$

import os
import sys
import adodbapi
#import pyodbc

class connDB():
    def __init__(self, dbfile=""):
        
        #self._thisProgramDir = os.path.dirname(__file__)
        #self._thisProgramDir = os.path.dirname(sys.executable)
        self._thisProgramDir = os.path.dirname(sys.argv[0])
        
        # Connect to Access;
        # jowang added;
        if dbfile == "":
            self.dbfile = os.path.join(self._thisProgramDir , u'meta_lib.mdb')
        else:
            self.dbfile = dbfile
            
        self.connStr = u'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s' % self.dbfile
        # connStr = u'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s' % u'D:\SMARTER\pysds\meta_lib.mdb'
        # connStr = u'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s' % u'D:\pysds\trunk\meta_lib.mdb'
        self.conn = adodbapi.connect(self.connStr)
        #self.conn = pyodbc.connect(self.connStr)
        
        self.mycursor = self.conn.cursor()
        
        
        # self.mycursor.execute("select * from SDTM_DATASETS")
        self.mycursor.execute("select DATASET,DESCRIPTION,CLASS,STRUCTURE,PURPOSE,KEYVAR,COMMENTS,VERSION from SDTM_DATASETS")
        
        self.rs = self.mycursor.fetchall()
        self.colheaders = self.mycursor.description
        
        #headers = [colname[0] for colname in colheaders]
        self.headers = [(colname[0],colname[3]) for colname in self.colheaders]
        self.rows = [_rows for _rows in self.rs]
        self.firstcol = [_col[0] for _col in self.rs]
        
        # Class list
        self.mycursor.execute("select CLASS from SDTM_CLASS_ORDER")
        self.rs = self.mycursor.fetchall()
        self.classlst = [_col[0] for _col in self.rs]
        self.classlst.sort()
        
        # to do
        # Tabulation list
        
        # Version list
        
        
    def Execute(self, sqlstr, *args, **kwargs):
        self.mycursor.execute(sqlstr, *args, **kwargs)
        
    def Commit(self, *args, **kwargs):
        self.conn.commit(*args, **kwargs)
        
        
    def FetchOne(self):
        self.rs = self.mycursor.fetchone()
        
        
    def FetchAll(self):
        self.rs = self.mycursor.fetchall()
        
        #return rs
    
    def GetHeaders(self):
        colheaders = self.mycursor.description
        
        headers = [(colname[0],colname[3]) for colname in colheaders]
        
        return headers    
    
    def GetFirstCol(self):
        
        firstcol = [_col[0] for _col in self.rs]
        
        return firstcol
        
    def GetRows(self):
        
        if self.rs is not None:
            rows = [_rows for _rows in self.rs]
        
            return rows
        
        else:
            
            return None
        
        
    def Close(self):
        """
        Close connection to database
        """
        self.mycursor.close()
        self.conn.close() 
